﻿Imports System.Data.OleDb
Imports Subgurim.Controles

Partial Public Class Details
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim OleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("RUHRestaurantsDB").ConnectionString)
        OleDbConn.Open()

        Dim RestId As Integer = Request.Params("Id")

        Dim checkRestID As String = "SELECT [Id]FROM [Resturants] WHERE [Id]=" & RestId & ""

        Dim checkRest = New OleDbCommand(checkRestID, OleDbConn)
        Dim RCheckID = checkRest.ExecuteReader()
        If RCheckID.HasRows Then
            While RCheckID.Read()
                Dim myRid As Integer = RCheckID.Item("Id")
                myRid = Session("RIDcheck")
            End While
            RCheckID.Close()

            Label5.Text = Session.Item("RIDcheck")
        Else
            Response.Redirect("List.aspx")
            ' Label5.Text = "No data"
        End If
     

        If String.IsNullOrEmpty(Request.Params("Id")) Then
            Response.Redirect("List.aspx")

        Else

            SQLResturantDetails.SelectCommand = "SELECT [Resturants.Id] AS ResturantId, [RestName], [Address], [Telephone], [Email], [PriceLevel], [LuxuryLevel], [EatingService], [WorkingHours], [UserName] FROM [ResturantsDetails] WHERE Resturants.Id=" & RestId & ""
            SqlDataSource1.SelectCommand = "SELECT [RestId], [ImgName] FROM [Rest_Images] WHERE RestId=" & RestId & ""

            Foodtype.SelectCommand = "SELECT FoodTypes.FoodType, FoodTypes.Description FROM (Rest_Food_Type INNER JOIN FoodTypes ON Rest_Food_Type.FoodTypeId = FoodTypes.Id) WHERE Rest_Food_Type.RestId =" & RestId & ""
            Services.SelectCommand = "SELECT  Facilities.Internet, Facilities.Parking, Facilities.Delivery, Facilities.Toilet, Facilities.ChildChair, Facilities.TV, Facilities.Music, Facilities.PlayingArea FROM (Facilities INNER JOIN Resturants ON Facilities.RestId = Resturants.Id) WHERE Facilities.RestId = " & RestId & ""

            comments.SelectCommand = "SELECT [Title], [Comments], [UserId], [Id],[date] FROM [Comments] WHERE RestId=" & RestId & ""

            Dim checkcomments As String = "SELECT [Title], [Comments], [UserId], [Id],[date] FROM [Comments] WHERE RestId=" & RestId & ""



            Dim CommConnect = New OleDbCommand(checkcomments, OleDbConn)
            Dim Cread = CommConnect.ExecuteReader
            If Not Cread.HasRows Then
                LBcomments.Text = "There is no comment wrote about this restaurant yet"
            End If

            Rating2.CurrentRating = RateAVG(OleDbConn, RestId)

            Dim cmdQuery As String = "SELECT Tag from Tags"

            Dim dbcomm = New OleDbCommand(cmdQuery, OleDbConn)

            Dim dbread = dbcomm.ExecuteReader()

            If dbread.HasRows Then

                While dbread.Read()
                    Dim hlink As New HyperLink
                    Dim TagName = dbread.Item("Tag")
                    hlink.Text = TagName
                    hlink.NavigateUrl = "javascript:AddTagToComment('" & TagName & "')"
                    ExistingTags.Controls.Add(hlink)
                    ExistingTags.Controls.Add(New LiteralControl("&nbsp;&nbsp;"))

                End While

            End If

            dbread.Close()


            'Google Map for the restaurant 
            GMap1.setCenter(New GLatLng(24.69943, 46.72348))
            Dim options As New GMapUIOptions()
            options.maptypes_physical = False
            options.zoom_scrollwheel = True
            GMap1.addGMapUI(New GMapUI())
            GMap1.GZoom = 10

            Dim location As String = "SELECT [RestName] , [Address] FROM Resturants Where id=" & RestId & ""
            Dim SqlLocation As OleDbCommand = New OleDbCommand(location, OleDbConn)

            Dim dblocation As OleDbDataReader = SqlLocation.ExecuteReader

            If dblocation.HasRows Then

                While dblocation.Read()

                    Dim addrs As String = dblocation.Item("Address")
                    addrs = (addrs.Replace("(", " ")).Replace(")", " ").Trim()
                    Dim address As String() = addrs.Split(",")

                    Dim center As New GLatLng(address(0), address(1))

                    GMap1.setCenter(center, 14)
                    Dim icon As New GMarker(center)
                    Dim window As New GInfoWindow(icon, "<br>" & dblocation.Item("RestName") & "</b>", False, GListener.Event.click)
                    GMap1.addInfoWindow(window)

                End While

            End If

            dblocation.Close()
            OleDbConn.Dispose()
            OleDbConn.Close()


        End If
    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
        Session("RESTID") = Request.Params("Id")
        Response.Redirect("Update.aspx")
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        Dim RestId As Integer = Request.Params("Id")
        Dim UsersTag = TagField.Text

        Dim OleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("RUHRestaurantsDB").ConnectionString)

        OleDbConn.Open()


        Dim cmdQuery As String = "SELECT Tag from Tags"
        Dim dbcomm = New OleDbCommand(cmdQuery, OleDbConn)
        Dim dbread = dbcomm.ExecuteReader()
        Dim allTagsConcatenated = ""
        While dbread.Read()
            allTagsConcatenated = allTagsConcatenated & dbread.Item("Tag")
        End While

        Dim newTags As ArrayList = New ArrayList()
        Dim oldTags As ArrayList = New ArrayList()
        Dim TagArray = UsersTag.Split(" ")

        For i = 0 To TagArray.Length - 1
            If allTagsConcatenated.Contains(TagArray(i)) Then
                oldTags.Add(TagArray(i))
            Else
                newTags.Add(TagArray(i))
            End If

        Next

        Dim TagCmd As String = "Insert into tags ([Tag]) Values(@Tag)"
        Dim RestTag As String = "Insert into Rest_tags ([TagId],[RestId]) Values(@TagId,@RestId)"
        Dim CommentsCmd As String = "INSERT INTO Comments([RestId],[UserId],[Title],[Comments],[Date],[Rate]) VALUES(@RestId,@UserId,@Title,@Comments,@Date,@Rate)"


        Dim TagcmdInsert As OleDbCommand = New OleDbCommand(TagCmd, OleDbConn)
        TagcmdInsert.CommandType = CommandType.Text

        Dim GetLatestTagId As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", OleDbConn)
        GetLatestTagId.CommandType = CommandType.Text

        Dim RestTagInsert As OleDbCommand = New OleDbCommand(RestTag, OleDbConn)
        RestTagInsert.CommandType = CommandType.Text

        Dim Comcmd As OleDbCommand = New OleDbCommand(CommentsCmd, OleDbConn)
        Comcmd.CommandType = CommandType.Text

        Dim findTagCmd As OleDbCommand = New OleDbCommand("SELECT ID From Tags WHERE Tag = ?", OleDbConn)
        findTagCmd.CommandType = CommandType.Text


        For i = 0 To newTags.Count - 1
            TagcmdInsert.Parameters.Clear()
            TagcmdInsert.Parameters.AddWithValue("Tag", newTags(i))
            TagcmdInsert.ExecuteNonQuery()
            Dim TID As Integer = GetLatestTagId.ExecuteScalar()
            RestTagInsert.Parameters.Clear()
            RestTagInsert.Parameters.AddWithValue("@TagId", TID)
            RestTagInsert.Parameters.AddWithValue("@RestId", RestId)
            RestTagInsert.ExecuteNonQuery()
        Next

        For i = 0 To oldTags.Count - 1
            findTagCmd.Parameters.Clear()
            findTagCmd.Parameters.AddWithValue("Tag", oldTags(i))
            Dim TagReader = findTagCmd.ExecuteReader()
            TagReader.Read()
            If TagReader.HasRows() Then
                Dim TID As Integer = TagReader("ID")
                RestTagInsert.Parameters.Clear()
                RestTagInsert.Parameters.AddWithValue("@TagId", TID)
                RestTagInsert.Parameters.AddWithValue("@RestId", RestId)
                RestTagInsert.ExecuteNonQuery()
            End If
            TagReader.Close()
        Next

        Comcmd.Parameters.AddWithValue("@RestId", RestId)
        Comcmd.Parameters.AddWithValue("@UserId", User.Identity.Name)
        Comcmd.Parameters.AddWithValue("@Title", Titletxt.Text)
        Comcmd.Parameters.AddWithValue("@Comments", commentstxt.Text)
        Comcmd.Parameters.AddWithValue("@Date", DateTime.Now.Date)
        Comcmd.Parameters.AddWithValue("@Rate", Rating1.CurrentRating)

        Comcmd.ExecuteNonQuery()




        OleDbConn.Close()
        Response.Redirect("Details.aspx?Id=" & RestId)
    End Sub
    Protected Function RateAVG(ByVal DBC As OleDbConnection, ByVal RestId As Integer)

        Dim AVGratecmd As String = "SELECT AVG(rate) as RatingAVG FROM [Comments] WHERE RestId=" & RestId & ""
        Dim AVGrate = New OleDbCommand(AVGratecmd, DBC)
        Dim AVGDBread = AVGrate.ExecuteReader()

        AVGDBread.Read()

        Try
            Return CInt(AVGDBread("RatingAVG"))
        Catch ex As Exception
            Return 0
        End Try

    End Function


End Class